﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Maticsoft.DBUtility
{
    public partial class DALHandlerBase<T>
    {
        public DALHandlerBase()
        { }

        #region 属性
        private string _tablename;

        /// <summary>
        /// 表名
        /// </summary>
        public string TableName
        {
            set { _tablename = value; }
            get { return _tablename; }
        }
        #endregion

        #region  Method

        /// <summary>
        /// 是否存在该条件记录
        /// </summary>
        /// <param name="strWhere">Where条件</param>
        /// <returns>bool:True/False</returns>
        public virtual bool Exists(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(1) FROM {0}", TableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            return DbHelperSQL.Exists(strSql.ToString(), null);
        }

        /// <summary>
        /// 根据条件获取数量
        /// </summary>
        /// <param name="strWhere">Where条件</param>
        /// <returns>记录数</returns>
        public virtual int Count(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM {0}", TableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.AppendFormat(" WHERE {0}", strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), null);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">NetName实体对象</param>
        /// <returns>自动增长列</returns>
        public virtual int Add(T model)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();//Parameter参数
            StringBuilder strSql = new StringBuilder();//SQL语句
            StringBuilder column = new StringBuilder();//列字段
            StringBuilder columnvalue = new StringBuilder();//列字段对应的值
            strSql.AppendFormat("INSERT INTO {0}(", TableName);
            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                PropertyInfo pi = typeof(T).GetProperty(info.Name);
                if (pi.GetValue(model, null) == null) continue;

                column.AppendFormat(",{0}", info.Name);
                columnvalue.AppendFormat(",@{0}", info.Name);//添加字段

                SqlField[] field = pi.GetCustomAttributes(typeof(SqlField), false) as SqlField[];
                SqlParameter para = new SqlParameter(info.Name, field[0].SqlDbTypeName);
                if (field[0].Length > 0)
                    para.Size = field[0].Length;//设置SqlParrameter

                para.Value = pi.GetValue(model, null);//设置SqlParameter对应Value
                paralist.Add(para);
            }
            strSql.AppendFormat("{0}) VALUES ({1});SELECT @@IDENTITY;", Regex.Replace(column.ToString(), "^,", ""), Regex.Replace(columnvalue.ToString(), "^,", ""));

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), paralist.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model">model实体对象</param>
        /// <param name="strWhere">Where条件</param>
        /// <returns>修改条数</returns>
        public virtual int Update(T model, string strWhere)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();//Parameter参数
            StringBuilder strSql = new StringBuilder();
            StringBuilder column = new StringBuilder();
            strSql.AppendFormat("UPDATE {0} SET ", TableName);
            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                PropertyInfo pi = typeof(T).GetProperty(info.Name);
                if (pi.GetValue(model, null) == null) continue;

                column.AppendFormat(",{0}=@{0}", info.Name);//添加字段

                SqlField[] field = pi.GetCustomAttributes(typeof(SqlField), false) as SqlField[];
                SqlParameter para = new SqlParameter(info.Name, field[0].SqlDbTypeName);
                if (field[0].Length > 0)
                    para.Size = field[0].Length;//设置SqlParameter

                para.Value = pi.GetValue(model, null);//设置SqlParameter对应Value
                paralist.Add(para);
            }
            strSql.Append(Regex.Replace(column.ToString(), "^,", ""));
            if (!string.IsNullOrEmpty(strWhere))
                strSql.AppendFormat(" WHERE {0}", strWhere);

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), paralist.ToArray());
            return rows;
        }
        public virtual int ExecuteSql(string sql)
        {

            int rows = DbHelperSQL.ExecuteSql(sql);
            return rows;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="strWhere">Where条件</param>
        /// <returns>删除条数</returns>
        public virtual int DeleteList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("DELETE FROM {0}", TableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            return rows;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="strWhere">Where条件</param>
        public virtual T GetModelBystrWhere(string strWhere)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT  TOP 1 * FROM {0}", TableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }

            T model = System.Activator.CreateInstance<T>();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0][info.Name].ToString()))
                        model.GetType().GetProperty(info.Name).SetValue(model, ds.Tables[0].Rows[0][info.Name], null);
                }

                return model;
            }
            else
            {
                return default(T);
            }
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        /// <param name="Top">条数</param>
        /// <param name="strWhere">Where条件</param>
        /// <param name="filedOrder">filedOrder排序</param>
        /// <returns>数据集</returns>
        public virtual List<T> GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            if (Top > 0)
            {
                strSql.AppendFormat("TOP {0} ", Top);
            }
            strSql.AppendFormat("* FROM {0}", TableName);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            if (!string.IsNullOrEmpty(filedOrder))
            {
                strSql.Append(" ORDER BY " + filedOrder);
            }

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataTableToList(ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// DataTable转List
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public virtual List<T> DataTableToList(DataTable dt)
        {
            List<T> list = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                T model = System.Activator.CreateInstance<T>();
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    if (!string.IsNullOrEmpty(row[info.Name].ToString()))
                        model.GetType().GetProperty(info.Name).SetValue(model, row[info.Name], null);
                }

                list.Add(model);
            }

            return list;
        }
        #endregion  Method
    }
}
